<?php

namespace App\Models;

use App\Models\Common\Constant;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;
use phpDocumentor\Reflection\DocBlock\Tags\Var_;

class ProductModel extends BaseModel
{
    public function getList($params){
        $where = '1 = 1';
        if (!empty($params['platform_id'])) {
       //     $where .= " and p.platform_id = {$params['platform_id']}";
            $where .= " and pf.mark = {$params['platform_id']}";
        }
        if (!empty($params['shop_id'])) {
            $where .= " and p.shop_id = {$params['shop_id']}";
        }
        if (!empty($params['product_name'])) {
            $where .= " and p.product_name like '%{$params['product_name']}%'";
        }
        if (!empty($params['parent_sku'])) {
            $where .= " and p.parent_sku like '%{$params['parent_sku']}%'";
        }

        $page = isset($params['page']) ? $params['page'] : 1;
        $limit = isset($params['limit']) ? $params['limit'] : 15;
        $page = $this->getLimitParam($page,$limit);
        $sql = "SELECT SQL_CALC_FOUND_ROWS
                    p.Id,
                    p.platform_id,
                    p.shop_id,
                    p.product_id_pt,
                    p.cat_id,
                    p.product_name,
                    p.parent_sku,
                    p.main_image,
                    p.product_description,
                    p.is_on_sale,
                    pf.mark,
                    s.shop_name,
                    c.name as category_name
                FROM
                    product p
                LEFT JOIN 
                    platform pf
                ON 
                    p.platform_id = pf.Id
                LEFT JOIN 
                    shop s
                ON 
                    p.shop_id = s.Id
                LEFT JOIN 
                    category c
                ON 
                    p.cat_id = c.Id
                WHERE $where
                ORDER BY
                    add_time DESC 
                    LIMIT $page,
                    $limit";
        $data = DB::select($sql);
        $totalNum = db::select("SELECT FOUND_ROWS() as total")[0]->total;
        foreach ($data as $k => $v) {
            $sql = "select mark from platform where Id = $v->platform_id limit 1";
            $result = DB::select($sql);
            //1.wish，2.vova，3.lazada
            if (!empty($result)) {
                $result = $result[0];
                switch ($result->mark) {
                    case 1:
                        $data[$k]->platform_name = 'wish';
                        break;
                    case 2:
                        $data[$k]->platform_name = 'vova';
                        break;
                    case 3:
                        $data[$k]->platform_name = 'lazada';
                        break;
                    default:

                }
            } else {
                $data[$k]->platform_name = '';
            }
        }
        return [
            'data' => $data,
            'totalNum' => $totalNum
        ];
    }

    public function getBarcode3($params){
        $where = '1 = 1';
        if (!empty($params['platform_id'])) {
            //     $where .= " and p.platform_id = {$params['platform_id']}";
            $where .= " and p.platform_id = {$params['platform_id']}";
        }
        if (!empty($params['shop_id'])) {
            $where .= " and p.shop_id = {$params['shop_id']}";
        }
        if (!empty($params['product_sku'])) {
            $where .= " and p.sellersku like '%{$params['product_sku']}%'";
        }
        if (!empty($params['product_fnsku'])) {
            $where .= " and p.fnsku like '%{$params['product_fnsku']}%'";
        }
        if (!empty($params['product_asin'])) {
            $where .= " and p.asin like '%{$params['product_asin']}%'";
        }
        if (!empty($params['product_key'])) {
            $product_key=$params['product_key'];
            $keySql = "select sku
                from saihe_product
                where product_name_cn like '%{$product_key}%'
                ";
            $key = json_decode(json_encode(DB::select($keySql)), true);
            $skulist=array();
            foreach ($key as $v){
                $skulist[]="'".trim($v['sku'])."'";
            }
            if(isset($skulist)) $saihesku=implode(",",$skulist);

            $where .= " and spd.saihe_sku in ({$saihesku})";
        }
        if (!empty($params['order_source_sku'])) {
            //库存sku搜索
            $order_source_sku=explode(",",$params['order_source_sku']);
            $str = '';
            foreach ($order_source_sku as $k=>$v){
                /*if($k==0){
                    $where.=" and spd.order_source_sku like '%{$v}%'";
                }else{
                    $where.=" or spd.order_source_sku like '%{$v}%'";
                }*/
                $str .= " spd.order_source_sku like "."'%{$v}%' or";

            }
            $newStr = substr($str,0,strlen($str)-2);
            $where.=" and {$newStr}";

        }



        $page = isset($params['page']) ? $params['page'] : 1;
        $limit = isset($params['limit']) ? $params['limit'] : 15;
        $page = $this->getLimitParam($page,$limit);

//        $sql = "select SQL_CALC_FOUND_ROWS p.platform_id,p.shop_id,p.sellersku,p.fnsku,p.asin,p.total_supply_quantity,p.instock_supply_quantity,
//       pf.name,s.shop_name,
//       spd.saihe_sku,spd.order_source_sku
//                from product_detail p
//                join product_sku ps on p.product_sku_id = ps.Id
//                join saihe_product_detail spd on p.asin = spd.asin
//                join platform pf on p.platform_id=pf.Id
//                join shop s on p.shop_id = s.Id
//                where $where order by p.id desc LIMIT $page,$limit";

        $sql = "select SQL_CALC_FOUND_ROWS p.platform_id,p.shop_id,p.sellersku,p.fnsku,p.asin,p.total_supply_quantity,p.instock_supply_quantity,
       spd.saihe_sku,spd.order_source_sku
                from product_detail p
                join saihe_product_detail spd on p.asin = spd.asin
                where $where order by p.id desc LIMIT $page,$limit";
        $data = DB::select($sql);
        $totalNum = db::select("SELECT FOUND_ROWS() as total")[0]->total;

//        foreach ($data as $key=>$value){
//            $saihe_sku = $value->saihe_sku;
//            $saiHeSql = "select product_name_cn
//                from saihe_product
//                where sku = {$saihe_sku}
//                ";
//            $saiHe = json_decode(json_encode(DB::select($saiHeSql)), true);
//            $data[$key]->product_name_cn = isset($saiHe[0]['product_name_cn'])?"{$saiHe[0]['product_name_cn']}":'';
//        }

        $skuMap = array_column($data,'saihe_sku');
        $skuMap = '"'.implode('","',$skuMap).'"';

        $shopIdMap = array_unique(array_column($data,'shop_id'));
        $shopIdMap = implode(',',$shopIdMap);

        $saiheSkuQuery = json_decode(json_encode(DB::select(
            "select sku,product_name_cn,small_image_url from saihe_product where sku in ({$skuMap})"
        )), true);
        $saiheSkuMap = [];
        foreach ($saiheSkuQuery as $value){
            $saiheSkuMap[$value['sku']] = [
                'product_name_cn' => $value['product_name_cn'],
                'small_image_url' => $value['small_image_url'],
            ];
        }
        $shopMap = [];
        if ($shopIdMap) {
            $shopQuery = json_decode(json_encode(DB::select(
                "select Id,shop_name from shop where Id in ({$shopIdMap})"
            )), true);

            foreach ($shopQuery as $value){
                $shopMap[$value['Id']] = $value['shop_name'];
            }
        }

        foreach ($data as &$value){
            $value->product_name_cn = isset($saiheSkuMap[$value->saihe_sku]['product_name_cn']) ? $saiheSkuMap[$value->saihe_sku]['product_name_cn'] : '';
            $value->shop_name = isset($shopMap[$value->shop_id]) ? $shopMap[$value->shop_id] : '';
            $value->small_image_url = isset($saiheSkuMap[$value->saihe_sku]['small_image_url']) ? $saiheSkuMap[$value->saihe_sku]['small_image_url'] : '';
        }

        return [
            'data' => $data,
            'totalNum' => $totalNum
        ];
    }


    public function getBarcodec($params){
        $where = '1 = 1';
        if (!empty($params['platform_id'])) {
            //     $where .= " and p.platform_id = {$params['platform_id']}";
            $where .= " and p.platform_id = {$params['platform_id']}";
        }


        if (!empty($params['shop_id'])) {
            $where .= " and p.shop_id = {$params['shop_id']}";
        }
        if (!empty($params['product_sku'])) {
            $where .= " and p.sellersku like '%{$params['product_sku']}%' ";
        }
        $sellersku_arr = '';
        if(isset($params['sellersku_arr'])){
            $where .= " and p.sellersku in ({$params['sellersku_arr']}) order by field(sellersku,{$params['sellersku_arr']})";
            $sellersku_arr = $params['sellersku_arr'];
        }
        if (!empty($params['product_fnsku'])) {
            $where .= " and p.fnsku like '%{$params['product_fnsku']}%'";
        }
        if (!empty($params['product_asin'])) {
            $where .= " and p.asin like '%{$params['product_asin']}%'";
        }
        if (!empty($params['store_name'])) {
            $where .= " and spd.order_source_name like '%{$params['store_name']}%'";
        }
        if (!empty($params['product_key'])) {
            $product_key=$params['product_key'];

            $keySql = "select sku
                from saihe_product
                where product_name_cn like '%{$product_key}%'
                ";
            $key = json_decode(json_encode(DB::select($keySql)), true);
            $skulist=array();
            foreach ($key as $v){
                $skulist[]="'".trim($v['sku'])."'";
            }

            if(isset($skulist)) $saihesku=implode(",",$skulist);

            $where .= " and spd.saihe_sku in ({$saihesku}) order by field(spd.saihe_sku,{$saihesku})";
        }
        $is_total=false;
        if (!empty($params['order_source_sku'])) {
            //库存sku搜索
            // $order_source_sku=explode(",",$params['order_source_sku']);
            // $str = '';
            // foreach ($order_source_sku as $v){

            //     $str.="spd.client_sku like '%{$v}%' or";
            // }
            // $newStr = substr($str,0,strlen($str)-2);
            // $where.=" and {$newStr}";

            $is_total=true;

            $cusres = DB::table('self_custom_sku')->where('custom_sku','like',"%{$params['order_source_sku']}%")->orwhere('old_custom_sku','like',"%{$params['order_source_sku']}%")->get();
            // if($cusres){
            //     $where.=" and sk.custom_sku = '{$cusres->custom_sku}' ";
            // }

            foreach ($cusres as $key => $value) {
                # code...
                if($key==0){
                    $where.=" and sk.custom_sku = '{$value->custom_sku}' ";
                }else{
                    $where.=" or sk.custom_sku = '{$value->custom_sku}' ";
                }

            }


            //   $skuMap = ""'.implode('","',$order_source_sku).'"';
//            $sql = "select sku from saihe_product where client_sku in ({$skuMap})";
//            $sql = "select sku from saihe_product where {$str}";
//            $data = DB::select($sql);
//            $skuMap ='';
//            if (!empty($data)){
//                $skuMap = array_column($data,'sku');
//                $where.=' and spd.saihe_sku in (  "'.implode('","',$skuMap).'")';
//            }else{
//                $where.=' and spd.saihe_sku =''';
//            }
        }

        // if(!stripos($where,'order by')){
        //     $where.=" order by spd.client_sku asc";
        // }
        $page = isset($params['page']) ? $params['page'] : 1;
        $limit = isset($params['limit']) ? $params['limit'] : 30;
        $page = $this->getLimitParam($page,$limit);

//        $sql = "select SQL_CALC_FOUND_ROWS p.platform_id,p.shop_id,p.sellersku,p.fnsku,p.asin,p.total_supply_quantity,p.instock_supply_quantity,
//       pf.name,s.shop_name,
//       spd.saihe_sku,spd.order_source_sku
//                from product_detail p
//                join product_sku ps on p.product_sku_id = ps.Id
//                join saihe_product_detail spd on p.asin = spd.asin
//                join platform pf on p.platform_id=pf.Id
//                join shop s on p.shop_id = s.Id
//                where $where order by p.id desc LIMIT $page,$limit";



            if($is_total){
                $sql = "select SQL_CALC_FOUND_ROWS DISTINCT(p.id),p.id,p.platform_id,p.shop_id,p.sellersku,p.fnsku,p.asin,p.total_supply_quantity,p.instock_supply_quantity,
                s.shop_name,p.title
                         from product_detail p
                         left join shop s on s.Id = p.shop_id
                         left join self_sku sk on sk.sku = p.sellersku or sk.old_sku = p.sellersku
                         where $where LIMIT $page,$limit";
                $data = DB::select($sql);
                $totalNum = db::select("SELECT FOUND_ROWS() as total")[0]->total;

            }else{
                $sql = "select SQL_CALC_FOUND_ROWS DISTINCT(p.id),p.id,p.platform_id,p.shop_id,p.sellersku,p.fnsku,p.asin,p.total_supply_quantity,p.instock_supply_quantity,
                s.shop_name,p.title
                         from product_detail p
                         left join shop s on s.Id = p.shop_id
                         where $where LIMIT $page,$limit";
                $data = DB::select($sql);
                $totalNum = db::select("SELECT FOUND_ROWS() as total")[0]->total;
            }




        // $skuMap = array_column($data,'saihe_sku');
        // $skuMap = '"'.implode('","',$skuMap).'"';


        // $saiheSkuQuery = json_decode(json_encode(DB::select(
        //     "select sku,product_name_cn,small_image_url from saihe_product where sku in ({$skuMap})"
        // )), true);
        // $saiheSkuMap = [];
        // foreach ($saiheSkuQuery as $value){
        //     $saiheSkuMap[$value['sku']] = [
        //         'product_name_cn' => $value['product_name_cn'],
        //         'small_image_url' => $value['small_image_url'],
        //     ];
        // }




        foreach ($data as &$value){
            // $value->product_name_cn = isset($saiheSkuMap[$value->saihe_sku]['product_name_cn']) ? $saiheSkuMap[$value->saihe_sku]['product_name_cn'] : '';
            // $value->small_image_url = isset($saiheSkuMap[$value->saihe_sku]['small_image_url']) ? $saiheSkuMap[$value->saihe_sku]['small_image_url'] : '';


            //新查询
            $skures = DB::table('self_sku')->where('sku',$value->sellersku)->orwhere('old_sku',$value->sellersku)->first();
            if($skures){
                $customres = DB::table('self_custom_sku')->where('custom_sku',$skures->custom_sku)->orwhere('old_custom_sku',$skures->custom_sku)->first();
                if( $customres){
                    $value->client_sku = $customres->old_custom_sku??$customres->custom_sku;
                }

                $cusres = DB::table('self_custom_sku')->where('custom_sku',$skures->custom_sku)->orwhere('old_custom_sku',$skures->custom_sku)->first();
                if($cusres){
                    $value->product_name_cn = $cusres->name;
                    $value->small_image_url = $cusres->img;
                }
            }else{

                $skulist = DB::table('amazon_skulist')->where('sku',$value->sellersku)->first();
                if($skulist){
                    $value->client_sku = $skulist->custom_sku.'(未绑定)';
                }else{
                    $value->client_sku = '';
                }

            }


        }

        return [
            'data' => $data,
            'totalNum' => $totalNum,
            'sellersku_arr' => $sellersku_arr
        ];
    }
    public function getBarcode($params){

        $data = DB::table('self_sku as sk')->leftjoin('self_custom_sku as sc','sc.id', '=', 'sk.custom_sku_id');


        // $is_total = 0;

        if (!empty($params['shop_id'])) {
            // $is_total = 1;
            $data = $data->where('sk.shop_id',$params['shop_id']);

        }
        if (!empty($params['product_sku'])) {
            $data = $data->where(function ($query) use ($params){
                return $query->where('sk.sku','like','%'.$params['product_sku'].'%')
                    ->orwhere('sk.old_sku','like','%'.$params['product_sku'].'%');
            });
        }
        $sellersku_arr = '';
        if(isset($params['sellersku_arr'])){
            // $is_total = 2;
            // $sellersku_arr = explode(',', $params['sellersku_arr']);
            $sellersku_arr = $params['sellersku_arr'];
            // var_dump( $sellersku_arr);
            $skus = DB::table('self_sku')->whereIn('sku',$sellersku_arr)->orwhereIn('old_sku',$sellersku_arr)->get();

            // var_dump($skus);

            $ids = array();

                # code...
            foreach ($sellersku_arr as $sk =>$sv){
                foreach ($skus as $v) {
                    if($v->sku==$sv || $v->old_sku ==$sv){
                        $ids[$sk] = $v->id;
                    }
                }
            }

            // var_dump($ids);

            $ids_str = implode(',', array_map(
                function ($str) {
                    return sprintf("'%s'", $str);
                }, $ids
            )
            );

            if(strlen($ids_str)<10){
                return [
                    'data' => [],
                    'totalNum' => 0,
                    // 'sellersku_arr' => $sellersku_arr
                ];
            }

            // var_dump($ids_str);

            $data = $data->where(function ($query) use ($sellersku_arr, $ids_str) {
                $query->whereIn('sk.sku',$sellersku_arr)->orwhereIn('sk.old_sku',$sellersku_arr);
            })->orderByRaw(DB::raw("FIELD(sk.id,$ids_str) asc"));
//            $data = $data->whereIn('sk.sku',$sellersku_arr)->orwhereIn('sk.old_sku',$sellersku_arr)->orderByRaw(DB::raw("FIELD(sk.id,$ids_str) asc"));

        }
        $skuarr = array();
        if (!empty($params['product_fnsku'])) {
            $product_res = DB::table('product_detail')->where('fnsku','like','%'.$params['product_fnsku'].'%')->get(['sellersku']);
            if($product_res){
                foreach ($product_res as $key => $value) {
                    # code...
                    $skuarr[] = $value->sellersku;
                }
            }
            // $is_total = 2;
            // $data = $data->where('p.fnsku','like','%'.$params['product_fnsku'].'%');
            // $count = $count->where('p.fnsku','like','%'.$params['product_fnsku'].'%');
        }
        if (!empty($params['product_asin'])) {
            // $is_total = 2;
            // $data = $data->where('p.asin','like','%'.$params['product_asin'].'%');
            // $count = $count->where('p.asin','like','%'.$params['product_asin'].'%');
            $product_res = DB::table('product_detail')->where('asin','like','%'.$params['product_asin'].'%')->get(['sellersku']);
            if($product_res){
                foreach ($product_res as $key => $value) {
                    # code...
                    $skuarr[] = $value->sellersku;
                }
            }
        }

        $customskuarr = array();
        //中文名
        if (!empty($params['product_key'])) {
            // $is_total = 2;
            $sps = DB::table('saihe_product')->where('product_name_cn','like','%'.$params['product_key'].'%')->get(['client_sku']);
            if($sps){
                foreach ($sps as $key => $value) {
                    # code...
                    $customskuarr[] = $value->client_sku;
                }
            }

            $cus = DB::table('self_custom_sku')->where('name','like','%'.$params['product_key'].'%')->get(['custom_sku']);
            if($cus){
                foreach ($cus as $key => $value) {
                    # code...
                    $customskuarr[] = $value->custom_sku;
                }
            }

        }

        if (!empty($params['order_source_sku'])) {
            //库存sku搜索
            // $is_total = 2;
            $post_custom_sku = $params['order_source_sku'];
            $data = $data->where(function ($query) use ($post_custom_sku){
                return $query->where('sc.custom_sku','like','%'. $post_custom_sku.'%')->orwhere('sc.old_custom_sku','like','%'. $post_custom_sku.'%');
            });
//            $data = $data->where('sc.custom_sku','like','%'. $post_custom_sku.'%')->orwhere('sc.old_custom_sku','like','%'. $post_custom_sku.'%');

        }


        if(isset($customskuarr[0])){
            $data = $data->where(function ($query) use ($customskuarr){
                return $query->whereIn('sc.custom_sku',$customskuarr)->orwhereIn('sc.old_custom_sku', $customskuarr);
            });
//            $data = $data->whereIn('sc.custom_sku',$customskuarr)->orwhereIn('sc.old_custom_sku',$customskuarr);
        }
        if(isset($skuarr[0])){
            $data = $data->where(function ($query) use ($skuarr){
                return $query->whereIn('sk.sku',$skuarr)->orwhereIn('sk.old_sku', $skuarr);
            });
//            $data = $data->whereIn('sk.sku',$skuarr)->orwhereIn('sk.old_sku',$skuarr);
        }


        $pagenum = isset($params['page']) ? $params['page'] : 1;
        $limit = isset($params['limit']) ? $params['limit'] : 30;

        $page  =  $pagenum- 1;
        if($page!=0){
            $page  =  $page * $limit;
        }

        $totalNum  = $data->count();

        $data = $data->leftjoin('self_color_size as cz','sc.size','=','cz.identifying')->offset($page)->limit($limit);
        if(isset($params['sellersku_arr'])){

            $data = $data->get(['sk.id','sk.sku','sk.old_sku','sc.custom_sku','sc.old_custom_sku','sk.shop_id','sc.name as scname','sc.img']);

        }else{
            $data = $data->orderby('sk.create_time','DESC')
                ->orderBy('sk.spu','asc')
                ->orderby('sk.shop_id','ASC')
                ->orderby('sc.color','ASC')
                ->orderby('cz.sort','ASC')
                ->get(['sk.id','sk.sku','sk.old_sku','sc.custom_sku','sc.old_custom_sku','sk.shop_id','sc.name as scname','sc.img']);
        }

        // if($is_total==0){
        //     $totalNum  = DB::table('self_sku')->count();
        // }elseif($is_total==1){
        //     $totalNum  = DB::table('self_sku')->where('shop_id',$params['shop_id'])->count();
        // }else{

        // }



        foreach ($data as &$value) {
            # code...
            $value->small_image_url = '';
            $value->product_name_cn = '';
            $value->client_sku ='';

            $product = DB::table('product_detail')->where('sku_id',$value->id)->first();


            if($product){
                $value->id = $product->id;
                $value->fnsku = $product->fnsku;
                $value->asin = $product->asin;
                $value->title =  $product->title;
                $value->total_supply_quantity = $product->total_supply_quantity;
                $value->instock_supply_quantity =  $product->instock_supply_quantity;
            }else{
                $value->id = 0;
            }
            $value->small_image_url = '';
            $value->product_name_cn = '';

            if($value->old_custom_sku){
                $value->client_sku = $value->old_custom_sku;
            }else{
                $value->client_sku = $value->custom_sku;
            }
            // $value->old_spu = $value->old_spu??$value->spu;
            $value->small_image_url = $value->img??'';

            $value->product_name_cn = $value->scname??'';


            if($value->old_sku){
                $value->sellersku = $value->old_sku;
            }else{
                $value->sellersku = $value->sku;
            }
            // $value->sellersku = $value->old_sku??$value->sku;
            $value->shop_name = $this->GetShop($value->shop_id)['shop_name'];
        }

        // var_dump(DB::getQueryLog());

        return [
            'data' => $data,
            'totalNum' => $totalNum,
            // 'sellersku_arr' => $sellersku_arr
        ];
    }


    public function getBarcodeByCkNew($params){


        $data = DB::table('self_custom_sku as sc')
            ->leftjoin('self_spu as sp','sp.id','=','sc.spu_id')
            ->leftjoin('self_color_size as scsz','sc.size', '=', 'scsz.identifying')
            ->leftjoin('self_base_spu as bsp','bsp.id','=','sp.base_spu_id')
            ->leftjoin('self_spu_info as spi','bsp.id','=','spi.base_spu_id');

        if (isset($params['color'])) {
            $data = $data->whereIn('sc.color',$params['color']);
        }

        if (isset($params['size'])) {
            $data = $data->whereIn('sc.size',$params['size']);
        }

        if(isset($params['custom_sku'])){
            $params['custom_sku'] = str_replace('+',' ',$params['custom_sku']);
            $data = $data->where(function($query) use($params){
                $query->where('sc.custom_sku','like','%'.$params['custom_sku'].'%')
                    ->orwhere('sc.old_custom_sku','like','%'.$params['custom_sku'].'%');
            });
        }

        if (!empty($params['ex_custom_sku'])){
            $cusids = array();
            foreach ($params['ex_custom_sku'] as  $cus) {
                $cus_id = $this->GetCustomSkuId($cus);
                if($cusids){
                    $cusids[] = $cus_id;
                }
            }
            $data = $data->whereIn('sc.id',$cusids);
        }

        if(isset($params['spu_id'])){

            $data = $data->whereIn('sp.id',$params['spu_id']);
        }

        if(isset($params['ids'])){

            //导出查询
            $cus_str = implode(',', array_map(
                function ($str) {
                    return sprintf("'%s'", $str);
                }, $params['ids']
            )
            );
            $data = $data->whereIn('sc.id',$params['ids'])
            ->orderByRaw(DB::raw("FIELD(sc.id,$cus_str) asc"))
            // ->orderBy('sp.spu','desc')
            // ->orderby('sc.color','ASC')
            // ->orderby('scsz.sort','ASC')
            ->groupBy('sc.custom_sku')
            ->get(['sc.id','sc.custom_sku','sc.old_custom_sku','scs.english_name','scs.name as color_name','sc.size','sp.spu',
                'sp.old_spu','sc.name as scname','sc.img','bsp.class as self_type','spi.unit_price']);

            return $data;
        }


        if(!empty($params['one_cate_id'])){
            $data = $data->whereIn('sp.one_cate_id',$params['one_cate_id']);
        }

        if(!empty($params['two_cate_id'])){
            $data = $data->whereIn('sp.two_cate_id',$params['two_cate_id']);
        }

        if(!empty($params['three_cate_id'])){
            $data = $data->whereIn('sp.three_cate_id',$params['three_cate_id']);
        }

        if(!empty($params['type'])){
            $data = $data->where('sc.type',$params['type']);
        }


        $pagenum = isset($params['page']) ? $params['page'] : 1;
        $limit = isset($params['limit']) ? $params['limit'] : 30;

        $page  =  $pagenum- 1;
        if($page!=0){
            $page  =  $page * $limit;
        }

//         DB::connection()->enableQueryLog();

        $totalNum  = $data->count();
        $data = $data->offset($page)->limit($limit)
        ->orderBy('sp.spu','desc')
        ->orderby('sc.color','ASC')
        ->orderby('scsz.sort','ASC')
        ->groupBy('sc.custom_sku')
        ->get(['sc.id','sc.custom_sku','sc.old_custom_sku','sc.type','sc.color','sc.size','sp.spu','sp.old_spu','sc.name as scname','sc.img',
            'sp.one_cate_id','sp.two_cate_id','sp.three_cate_id','bsp.class as self_type','bsp.class','spi.unit_price']);
//            print_r(DB::getQueryLog());




        foreach ($data as &$value) {
            $value->cate_ids = $value->one_cate_id.'-'.$value->two_cate_id.'-'.$value->three_cate_id;
            $value->color_name = '';
            $value->english_name = '';
            $colors = db::table('self_color_size')->where('identifying',$value->color)->first();
            if($colors){
                $value->color_name = $colors->name;
                $value->english_name = $colors->english_name;
            }
            # code...
            // if($value->old_sku){
            //     $product = DB::table('product_detail')->where('sellersku',$value->sku)->orwhere('sellersku',$value->old_sku)->first();
            // }else{
            //     $product = DB::table('product_detail')->where('sellersku',$value->sku)->first();
            // }
            // $value->id = 0;
            // $custom_sku = $value->old_custom_sku??$value->custom_sku;
            // $value->custom_sku = $custom_sku;

            // $value->small_image_url = $value->img??'';
            $value->small_image_url = $this->GetCustomskuImg($value->custom_sku);
            $value->product_name_cn = '';

            // if($product){
            //     $value->id = $product->id;
            //     $value->small_image_url = $value->img??$product->small_image_url;
            // }else{
            //     $value->small_image_url = $value->img??'';
            // }

            if($value->old_custom_sku){
                $value->client_sku = $value->old_custom_sku;
            }else{
                $value->client_sku = $value->custom_sku;
            }
            // $value->test_cus = if $value->old_custom_sku ??$value->custom_sku;

            if($value->old_spu){
                $value->spu = $value->old_spu;
            }else{
                $value->spu = $value->spu;
            }
            // $value->old_spu = $value->old_spu??$value->spu;

            $value->product_name_cn = $value->scname??'';

            // $value->sellersku = $value->old_sku??$value->sku;
            // $value->shop_name = $this->GetShop($value->shop_id)['shop_name'];
        }

        return [
            'data' => $data,
            'totalNum' => $totalNum,
        ];
    }


    //订单出库列表导入查询
    public function BarcodePlatformExcelImport($params)
    {
        $p['row'] = ['custom_sku','num'];
        $p['file'] = $params['file'];
        $p['start'] = 2;
        $platform_id = $params['platform_id'];
        $warehouse_id = $params['warehouse_id'];
        $type = $params['type']??1; //1出 2入
        $data =  $this->CommonExcelImport($p);
        $sku_ids = [];
        $sku_res = [];
        $sku_repeat = [];
        $err = [];
        foreach ($data as $v) {
            # code...
            if(!empty($v['custom_sku'])){
                $sku_id = $this->GetCustomSkuId($v['custom_sku']);
                if($sku_id==0){
                  $err[] = $v['custom_sku'];
                }
                $sku_ids[] = $sku_id;
                $sku_res[$sku_id] = $v['num'];
                // if(isset($sku_repeat[$sku_id])){
                //     return ['msg'=>'重复库存sku'.$sku_repeat[$sku_id].'-'.$v['custom_sku'],'type'=>'fail'];
                // }else{
                //     $sku_repeat[$sku_id] = $v['custom_sku'];
                // }
            }
        }

        if(count($err)>0){
            return ['msg'=>'库存sku不存在'.implode(',',$err),'type'=>'fail'];
        }
       
             
        $data = $this->getBarcodePlatform(['sku_ids'=>$sku_ids,'sku_res'=>$sku_res,'platform_id'=>$platform_id,'warehouse_id'=>$warehouse_id]);


        $new_data = [];

        if($type==1){
            //intsock_num
            foreach ($data['data'] as $v) {
                # code...
                // if($v->intsock_num>0){
                    $new_data[] = $v;
                // }
            }
        }else{
            $new_data = $data['data'];
        }

        return ['msg'=>'导入成功','type'=>'success','data'=>$new_data];

    }


    //订单出库列表
    public function getBarcodePlatform($params)
    {
        $pagenum = isset($params['page']) ? $params['page'] : 1;
        $limit   = isset($params['limit']) ? $params['limit'] : 30;

        $page = $pagenum - 1;
        if ($page != 0) {
            $page = $page * $limit;
        }

        $query = DB::table('self_custom_sku as sc', 'sc.id', '=', 'sk.custom_sku_id')
            ->select([
                'sc.id',
                'sc.id as custom_sku_id',
                'sp.id as spu_id',
                'sc.custom_sku',
                'sc.old_custom_sku',
                'sc.type',
                'scs.english_name',
                'scs.name as color_name',
                'sc.size',
                'sp.spu',
                'sp.old_spu',
                'sc.name as product_name_cn'
            ])
            ->leftjoin('self_color_size as scs', 'sc.color', '=', 'scs.identifying')
            ->leftjoin('self_color_size as scsz', 'sc.size', '=', 'scsz.identifying')
            ->leftjoin('self_spu as sp', 'sp.id', '=', 'sc.spu_id');


        if (!empty($params['spu'])) {
            $spu = explode(',', $params['spu']);
            if (count($spu) > 1) {
                $query->where(function ($query) use ($spu) {
                    $query->whereIn('sp.spu', $spu)->orwhereIn('sp.old_spu', $spu);
                });
            } else {
                $query->where(function ($query) use ($spu) {
                    $query->where('sp.spu', $spu[0])->orwhere('sp.old_spu', $spu[0]);
                });
            }
        }

        if (!empty($params['custom_sku'])) {
            $params['custom_sku'] = str_replace('+', ' ', $params['custom_sku']);
            if (strstr($params['custom_sku'], '，')) {
                $symbol = '，';
            } elseif (strstr($params['custom_sku'], ',')) {
                $symbol = ',';
            } else {
                $symbol = '';
            }
            if ($symbol) {
                $cusarray = explode($symbol, $params['custom_sku']);
                $cusquery = array();
                foreach ($cusarray as $cus) {
                    $cusquery[] = $this->GetCustomSkuId(trim($cus));
                }
                $query->whereIn('sc.id', $cusquery);
            } else {
                $product = db::table('product_detail')->where('fnsku', 'like', '%' . $params['custom_sku'] . '%')->first();
                if ($product) {
                    $query->where('sc.id', $product->custom_sku_id);
                } else {
                    $query->where(function ($q) use ($params) {
                        $q->where('sc.custom_sku', 'like', '%' . $params['custom_sku'] . '%')
                            ->orwhere('sc.old_custom_sku', 'like', '%' . $params['custom_sku'] . '%');
                    });
                }
            }
        }

        if (!empty($params['custom_skus'])) {
            $customSkuArr   = explode(',', $params['custom_skus']);
            $customSkuIdArr = [];
            foreach ($customSkuArr as $customSku) {
                $customSkuId = DB::table('self_custom_sku')
                    ->where('custom_sku', $customSku)
                    ->orwhere('old_custom_sku', $customSku)
                    ->value('id');
                if ($customSkuId) {
                    $customSkuIdArr[] = $customSkuId;
                }
            }
            if (!$customSkuIdArr) {
                return ['data' => [], 'totalNum' => 0];
            }

            $query->whereIn('sc.id', $customSkuIdArr);
        }

        if (!empty($params['color'])) {
            $color = explode(',', $params['color']);
            if (count($color) > 1) {
                $query->whereIn('scs.english_name', $color);
            } else {
                $query->where('scs.english_name', $color[0]);
            }
        }

        if(isset($params['sku_ids'])){
            $query->whereIn('sc.id', $params['sku_ids']);
        }

        if (!empty($params['size'])) {
            $size = explode(',', $params['size']);
            if (count($size) > 1) {
                $query->whereIn('sc.size', $size);
            } else {
                $query->where('sc.size', $size[0]);
            }
        }

        if (!empty($params['type'])) {
            $query->where('sc.type', $params['type']);
        }

        $totalNum = $query->count();

        if ($totalNum == 0) {
            return ['data' => [], 'totalNum' => 0,];
        }

        if (isset($customSkuIdArr)) {
            $customSkuIdStr = implode(',', $customSkuIdArr);
            $query->orderByRaw("find_in_set(sc.id, '{$customSkuIdStr}')");
        }

        if(isset($params['sku_ids'])){
            $data = $query
            ->orderBy('sp.spu', 'desc')
            ->orderby('sc.color', 'ASC')
            ->orderby('scsz.sort', 'ASC')
            ->get();
        }else{
            $data = $query
            ->orderBy('sp.spu', 'desc')
            ->orderby('sc.color', 'ASC')
            ->orderby('scsz.sort', 'ASC')
            ->offset($page)
            ->limit($limit)
            ->get();
        }

  

        $platformId = $params['platform_id'];
        $warehouse_id = $params['warehouse_id'];

        $locationNum = DB::table('cloudhouse_location_num as a')
            ->leftJoin('cloudhouse_warehouse_location as b','a.location_id','=','b.id')
            ->select('a.custom_sku_id', 'a.num','b.type')
            ->where('b.warehouse_id',$warehouse_id)
            ->whereIn('custom_sku_id', $data->pluck('custom_sku_id'));
        $locationNum = $locationNum->where('platform_id', $platformId);



        $locationNum = $locationNum->get();


        $inventoryArr = [];
        $inventoryBad = [];
        foreach ($locationNum as $v) {
            if($v->type==1) {
                if (isset($inventoryArr[$v->custom_sku_id])) {
                    $inventoryArr[$v->custom_sku_id] += $v->num;
                } else {
                    $inventoryArr[$v->custom_sku_id] = $v->num;
                }
            }else{
                if (isset($inventoryBad[$v->custom_sku_id])) {
                    $inventoryBad[$v->custom_sku_id] += $v->num;
                } else {
                    $inventoryBad[$v->custom_sku_id] = $v->num;
                }
            }
        }

        foreach ($data as $v) {
            if($v->old_custom_sku){
                $v->client_sku = $v->old_custom_sku;
            }else{
                $v->client_sku = $v->custom_sku;
            }
            $v->small_image_url = '';
            $img = $this->GetCustomskuImg($v->custom_sku);
            if($img){
                $v->small_image_url = $img;
            }

            $inventory  = $inventoryArr[$v->custom_sku_id] ?? 0;
            $inventory_bad = $inventoryBad[$v->custom_sku_id] ?? 0;
            $v->intsock_num = 0;
            if ($v->type != 2) {
                $otherInventory = $this->inventoryDetailByWlp($v->custom_sku_id, $warehouse_id, 0, $platformId);
                $v->intsock_num = $inventory -$otherInventory['lock_num']>0?$inventory -$otherInventory['lock_num']:0;//可用库存
                $v->bad_num = $inventory_bad;
            }

            if($v->type==2){
                $v->group_sku = [];
                $v->group_sku = $this->GetGroupCustomSku($v->custom_sku);
                $minGroupSkuIntsockNum = null;//最小组合sku库存;
                foreach ($v->group_sku as $key => $sku) {
                    $groupSkuInventory = $this->GetInventoryByPlatformAndSku($warehouse_id, $sku['group_custom_sku_id'], $platformId);
                    $groupSkuOtherInventory = $this->inventoryDetailByWlp($sku['group_custom_sku_id'], $warehouse_id, 0, $platformId);
                    $groupSkuIntsockNum = $groupSkuInventory -$groupSkuOtherInventory['lock_num']>0?$groupSkuInventory -$groupSkuOtherInventory['lock_num']:0;//可用库存
                    $v->group_sku[$key]['intsock_num'] = $groupSkuIntsockNum;


                    if (is_null($minGroupSkuIntsockNum)) {
                        $minGroupSkuIntsockNum = $groupSkuIntsockNum;
                    } else {
                        if ($groupSkuIntsockNum < $minGroupSkuIntsockNum) {
                            $minGroupSkuIntsockNum = $groupSkuIntsockNum;
                        }
                    }
                }

                $v->intsock_num = $minGroupSkuIntsockNum;
                $v->bad_num = 0;
            }

            $v->move_num = 0;
            if(isset($params['sku_res'][$v->custom_sku_id])){
                $v->move_num = (int)$params['sku_res'][$v->custom_sku_id];
            }
        }

        return [
            'data'     => $data,
            'totalNum' => $totalNum,
        ];
    }

    public function getBarcodeByCk($params){





        $data = DB::table('self_custom_sku as sc','sc.id', '=', 'sk.custom_sku_id')
            ->leftjoin('self_color_size as scs','sc.color', '=', 'scs.identifying')
            ->leftjoin('self_color_size as scsz','sc.size', '=', 'scsz.identifying')
            ->leftjoin('self_spu as sp','sp.id','=','sc.spu_id')
            ->leftjoin('self_spu_color as d','d.color_id','sc.color_id');
        $count = DB::table('self_custom_sku as sc','sc.id', '=', 'sk.custom_sku_id')->leftjoin('self_color_size as scs','sc.color', '=', 'scs.identifying')->leftjoin('self_spu as sp','sp.id','=','sc.spu_id');


        if (!empty($params['color'])) {

                if (strstr($params['color'], '，')) {
                    $symbol = '，';
                } elseif (strstr($params['color'], ',')) {
                    $symbol = ',';
                } else {
                    $symbol = '';
                }
                if ($symbol) {
                    $colorarray = explode($symbol,$params['color']);
                    $colorquery = array();
                    foreach ($colorarray as $key => $color) {
                        $colorquery[] = $color;
                    }
                    $data = $data->whereIn('scs.english_name',$colorquery);
                    $count = $count->whereIn('scs.english_name',$colorquery);

                } else {
                    $data = $data->where('scs.english_name',$params['color']);
                    $count = $count->where('scs.english_name',$params['color']);
                }
        }

        if(!empty($params['custom_sku'])){

            $params['custom_sku'] = str_replace('+',' ',$params['custom_sku']);

            if (strstr($params['custom_sku'], '，')) {
                $symbol = '，';
            } elseif (strstr($params['custom_sku'], ',')) {
                $symbol = ',';
            } else {
                $symbol = '';
            }
            if ($symbol) {
                $cusarray = explode($symbol,$params['custom_sku']);
                $cusquery = array();
                foreach ($cusarray as $cus) {
                    $cusquery[] = $this->GetCustomSkuId(trim($cus));
                }
                $data = $data->whereIn('sc.id',$cusquery);
                $count = $count->whereIn('sc.id',$cusquery);

            } else {

                $product = db::table('product_detail')->where('fnsku',$params['custom_sku'])->first();
                if($product){
                    $data = $data->where('sc.id',$product->custom_sku_id);
                    $count = $count->where('sc.id',$product->custom_sku_id);
                }else{
                    $data = $data->where('sc.custom_sku',$params['custom_sku'])->orwhere('sc.old_custom_sku',$params['custom_sku']);
                    $count = $count->where('sc.custom_sku',$params['custom_sku'])->orwhere('sc.old_custom_sku',$params['custom_sku']);
                }
            }
        }

        if (!empty($params['ex_custom_sku'])){
            // var_dump($params['ex_custom_sku']);
            $cuslist = array();
            foreach ($params['ex_custom_sku'] as  $cus) {
                # code...
                $new_cus = $this->GetNewCustomSku($cus);

                if($new_cus){
                    $cuslist[] = $new_cus;
                }
            }

            // var_dump($cuslist);


            $cus_str = implode(',', array_map(
                function ($str) {
                    return sprintf("'%s'", $str);
                }, $cuslist
            )
            );

            // exit;
            $data = $data->whereIn('sc.custom_sku',$cuslist )->orderByRaw(DB::raw("FIELD(sc.custom_sku,$cus_str) asc"))->groupBy('sc.custom_sku')
            ->get(['sc.id', 'sc.id as custom_sku_id', 'sc.custom_sku','sc.old_custom_sku','scs.english_name','sc.type','scs.name as color_name','sc.size','sp.spu', 'sp.id as spu_id', 'sp.old_spu','sc.name as scname','d.img as small_image_url']);
            $totalNum = $count->whereIn('sc.custom_sku',$cuslist)->groupBy('sc.custom_sku')->get(['sc.custom_sku'])->count();
            // $params['limit'] = $count;

        foreach ($data as &$value) {
            # code...
//            $value->small_image_url = $value->img??'';
            $value->product_name_cn = '';

            if($value->old_custom_sku){
                $value->client_sku = $value->custom_sku;
            }else{
                $value->client_sku = $value->custom_sku;
            }


            if($value->old_spu){
                $value->spu = $value->old_spu;
            }else{
                $value->spu = $value->spu;
            }


            $value->product_name_cn = $value->scname??'';

        }
            return [
                'data' => $data,
                'totalNum' => $totalNum,
            ];
        }


        if (!empty($params['size'])) {
            if (strstr($params['size'], '，')) {
                $symbol = '，';
            } elseif (strstr($params['size'], ',')) {
                $symbol = ',';
            } else {
                $symbol = '';
            }
            if ($symbol) {
                $sizearray = explode($symbol,$params['size']);
                $sizequery = array();
                foreach ($sizearray as $key => $size) {
                    $sizequery[] = $size;
                }
                $data = $data->whereIn('sc.size',$sizequery);
                $count = $count->whereIn('sc.size',$sizequery);
            } else {
                $data = $data->where('sc.size',$params['size']);
                $count = $count->where('sc.size',$params['size']);
            }

        }

        if(isset($params['ids'])){

            //导出查询
            $cus_str = implode(',', array_map(
                function ($str) {
                    return sprintf("'%s'", $str);
                }, $params['ids']
            )
            );
            $data = $data->whereIn('sc.id',$params['ids'])
            ->orderByRaw(DB::raw("FIELD(sc.id,$cus_str) asc"))
            // ->orderBy('sp.spu','desc')
            // ->orderby('sc.color','ASC')
            // ->orderby('scsz.sort','ASC')
            ->groupBy('sc.custom_sku')
            ->get(['sc.id', 'sc.id as custom_sku_id', 'sc.custom_sku','sc.old_custom_sku','scs.english_name','scs.name as color_name','sc.size','sp.spu', 'sp.id as spu_id','sp.old_spu','sc.name as scname','sc.img']);

            return $data;
        }
        if(!empty($params['spu'])){
            if (strstr($params['spu'], '，')) {
                $symbol = '，';
            } elseif (strstr($params['spu'], ',')) {
                $symbol = ',';
            } else {
                $symbol = '';
            }
            if ($symbol) {
                $cusarray = explode($symbol,$params['spu']);
                $cus_query = array();
                foreach ($cusarray as $key => $cus) {
                    $cus_query[] = $cus;
                }
                $data = $data ->where(function ($query) use($cus_query) {
                    $query->whereIn('sp.spu',$cus_query)->orwhereIn('sp.old_spu',$cus_query);
                    });
                $count = $count ->where(function ($query) use($cus_query){
                        $query->whereIn('sp.spu',$cus_query)->orwhereIn('sp.old_spu',$cus_query);
                    });
            } else {
                $cus = $params['spu'];
                $data = $data ->where(function ($query) use($cus) {
                    $query->where('sp.spu',$cus)->orwhere('sp.old_spu',$cus);
                    });
                $count = $count ->where(function ($query) use($cus) {
                        $query->where('sp.spu',$cus)->orwhere('sp.old_spu',$cus);
                    });
            }

        }

        if(!empty($params['one_cate_id'])){
            $data = $data->whereIn('sp.one_cate_id',$params['one_cate_id']);
        }

        if(!empty($params['two_cate_id'])){
            $data = $data->whereIn('sp.two_cate_id',$params['two_cate_id']);
        }

        if(!empty($params['three_cate_id'])){
            $data = $data->whereIn('sp.three_cate_id',$params['three_cate_id']);
        }

        if(!empty($params['type'])){
            $data = $data->where('sc.type',$params['type']);
        }


        $pagenum = isset($params['page']) ? $params['page'] : 1;
        $limit = isset($params['limit']) ? $params['limit'] : 30;

        $page  =  $pagenum- 1;
        if($page!=0){
            $page  =  $page * $limit;
        }

//         DB::connection()->enableQueryLog();

        $totalNum  = $data->groupBy('sc.custom_sku')->get()->count();
        $data = $data->offset($page)->limit($limit)
        ->orderBy('sp.spu','desc')
        ->orderby('sc.color','ASC')
        ->orderby('scsz.sort','ASC')
        ->groupBy('sc.custom_sku')
        ->get(['sc.id', 'sc.id as custom_sku_id', 'sp.id as spu_id','sc.custom_sku','sc.old_custom_sku','sc.type','scs.english_name','scs.name as color_name','sc.size','sp.spu','sp.old_spu','sc.name as scname','sc.img','sp.one_cate_id','sp.two_cate_id','sp.three_cate_id', 'sc.cloud_num']);
//            print_r(DB::getQueryLog());


        // $list = $data->toArrayList();
        $list = json_decode(json_encode($data),true);
        $customSkuIds = array_column($list, 'custom_sku_id');
        //获取锁定库存
        $requestres = $this->lock_inventory($customSkuIds);
        foreach ($data as &$value) {
            $value->tongan_request_num = 0;
            $value->quanzhou_request_num = 0;
            $value->cloud_request_num = 0;
            if(isset($requestres[$value->custom_sku_id][1])){
                $value->tongan_request_num = $requestres[$value->custom_sku_id][1];
            }
            if(isset($requestres[$value->custom_sku_id][2])){
                $value->quanzhou_request_num = $requestres[$value->custom_sku_id][2];
            }
            if(isset($requestres[$value->custom_sku_id][3])){
                $value->cloud_request_num = $requestres[$value->custom_sku_id][3];
            }
            $value->cloud_num = $value->cloud_num - $value->cloud_request_num;
            $value->cate_ids = $value->one_cate_id.'-'.$value->two_cate_id.'-'.$value->three_cate_id;
            # code...
            // if($value->old_sku){
            //     $product = DB::table('product_detail')->where('sellersku',$value->sku)->orwhere('sellersku',$value->old_sku)->first();
            // }else{
            //     $product = DB::table('product_detail')->where('sellersku',$value->sku)->first();
            // }
            // $value->id = 0;
            // $custom_sku = $value->old_custom_sku??$value->custom_sku;
            // $value->custom_sku = $custom_sku;

            // $value->small_image_url = $value->img??'';
            $value->small_image_url = $this->GetCustomskuImg($value->custom_sku);
            $value->product_name_cn = '';

            // if($product){
            //     $value->id = $product->id;
            //     $value->small_image_url = $value->img??$product->small_image_url;
            // }else{
            //     $value->small_image_url = $value->img??'';
            // }

            if($value->old_custom_sku){
                $value->client_sku = $value->custom_sku;
            }else{
                $value->client_sku = $value->custom_sku;
            }
            // $value->test_cus = if $value->old_custom_sku ??$value->custom_sku;

            if($value->old_spu){
                $value->spu = $value->old_spu;
            }else{
                $value->spu = $value->spu;
            }
            // $value->old_spu = $value->old_spu??$value->spu;

            $value->product_name_cn = $value->scname??'';

            if($value->type==2){
                $value->group_sku = [];
                $value->group_sku = $this->GetGroupCustomSku($value->custom_sku);
            }

            // $value->sellersku = $value->old_sku??$value->sku;
            // $value->shop_name = $this->GetShop($value->shop_id)['shop_name'];
        }

        return [
            'data' => $data,
            'totalNum' => $totalNum,
        ];
    }

    public function getBarcodeWare($params){


        $data = DB::table('self_custom_sku as sc','sc.id', '=', 'sk.custom_sku_id')
            ->leftjoin('self_color_size as scs','sc.color', '=', 'scs.identifying')
            ->leftjoin('self_color_size as scsz','sc.size', '=', 'scsz.identifying')
            ->leftjoin('self_spu as sp','sp.id','=','sc.spu_id')
            ->leftjoin('self_spu_color as d','d.color_id','sc.color_id');
        $count = DB::table('self_custom_sku as sc','sc.id', '=', 'sk.custom_sku_id')->leftjoin('self_color_size as scs','sc.color', '=', 'scs.identifying')->leftjoin('self_spu as sp','sp.id','=','sc.spu_id');


        if (!empty($params['color'])) {

            if (strstr($params['color'], '，')) {
                $symbol = '，';
            } elseif (strstr($params['color'], ',')) {
                $symbol = ',';
            } else {
                $symbol = '';
            }
            if ($symbol) {
                $colorarray = explode($symbol,$params['color']);
                $colorquery = array();
                foreach ($colorarray as $key => $color) {
                    $colorquery[] = $color;
                }
                $data = $data->whereIn('scs.english_name',$colorquery);
                $count = $count->whereIn('scs.english_name',$colorquery);

            } else {
                $data = $data->where('scs.english_name',$params['color']);
                $count = $count->where('scs.english_name',$params['color']);
            }
        }

        if(isset($params['cus_ids'])){
            $data = $data->whereIn('sc.id',$params['cus_ids']);
            $count = $count->whereIn('sc.id',$params['cus_ids']);
        }

        if(!empty($params['custom_sku'])){
            if (strstr($params['custom_sku'], '，')) {
                $symbol = '，';
            } elseif (strstr($params['custom_sku'], ',')) {
                $symbol = ',';
            } else {
                $symbol = '';
            }
            if ($symbol) {
                $cusarray = explode($symbol,$params['custom_sku']);
                $cusquery = array();
                foreach ($cusarray as $cus) {
                    $cusquery[] = $this->GetCustomSkuId(trim($cus));
                }
                $data = $data->whereIn('sc.id',$cusquery);
                $count = $count->whereIn('sc.id',$cusquery);

            } else {
                $data = $data->where('sc.custom_sku',$params['custom_sku'])->orwhere('sc.old_custom_sku',$params['custom_sku']);
                $count = $count->where('sc.custom_sku',$params['custom_sku'])->orwhere('sc.old_custom_sku',$params['custom_sku']);
            }
        }

        if (!empty($params['custom_skus'])) {
            $customSkuArr   = explode(',', $params['custom_skus']);
            $customSkuIdArr = [];
            foreach ($customSkuArr as $customSku) {
                $customSkuId = DB::table('self_custom_sku')
                    ->where('custom_sku', $customSku)
                    ->orwhere('old_custom_sku', $customSku)
                    ->value('id');
                if ($customSkuId) {
                    $customSkuIdArr[] = $customSkuId;
                }
            }
            $data  = $data->whereIn('sc.id', $customSkuIdArr);
            $count = $count->whereIn('sc.id', $customSkuIdArr);
        }

        if (!empty($params['ex_custom_sku'])){
            // var_dump($params['ex_custom_sku']);
            $cuslist = array();
            foreach ($params['ex_custom_sku'] as  $cus) {
                # code...
                $new_cus = $this->GetNewCustomSku($cus);

                if($new_cus){
                    $cuslist[] = $new_cus;
                }
            }



            $cus_str = implode(',', array_map(
                    function ($str) {
                        return sprintf("'%s'", $str);
                    }, $cuslist
                )
            );

            // exit;
            $data = $data->whereIn('sc.custom_sku',$cuslist )->orderByRaw(DB::raw("FIELD(sc.custom_sku,$cus_str) asc"))->groupBy('sc.custom_sku')
                ->get(['sc.id','sc.custom_sku','sc.old_custom_sku','scs.english_name','sc.type','scs.name as color_name','sc.size','sp.spu','sp.old_spu','sc.name as scname','d.img as small_image_url']);
            $totalNum = $count->whereIn('sc.custom_sku',$cuslist)->groupBy('sc.custom_sku')->get(['sc.custom_sku'])->count();
            // $params['limit'] = $count;

            foreach ($data as &$value) {
                # code...
//            $value->small_image_url = $value->img??'';
                $value->product_name_cn = '';

                if($value->old_custom_sku){
                    $value->client_sku = $value->old_custom_sku;
                }else{
                    $value->client_sku = $value->custom_sku;
                }


                if($value->old_spu){
                    $value->spu = $value->old_spu;
                }else{
                    $value->spu = $value->spu;
                }


                $value->product_name_cn = $value->scname??'';

            }
            return [
                'data' => $data,
                'totalNum' => $totalNum,
            ];
        }


        if (!empty($params['size'])) {
            if (strstr($params['size'], '，')) {
                $symbol = '，';
            } elseif (strstr($params['size'], ',')) {
                $symbol = ',';
            } else {
                $symbol = '';
            }
            if ($symbol) {
                $sizearray = explode($symbol,$params['size']);
                $sizequery = array();
                foreach ($sizearray as $key => $size) {
                    $sizequery[] = $size;
                }
                $data = $data->whereIn('sc.size',$sizequery);
                $count = $count->whereIn('sc.size',$sizequery);
            } else {
                $data = $data->where('sc.size',$params['size']);
                $count = $count->where('sc.size',$params['size']);
            }

        }

        if(isset($params['ids'])){

            //导出查询
            $cus_str = implode(',', array_map(
                    function ($str) {
                        return sprintf("'%s'", $str);
                    }, $params['ids']
                )
            );
            $data = $data->whereIn('sc.id',$params['ids'])
                ->orderByRaw(DB::raw("FIELD(sc.id,$cus_str) asc"))
                // ->orderBy('sp.spu','desc')
                // ->orderby('sc.color','ASC')
                // ->orderby('scsz.sort','ASC')
                ->groupBy('sc.custom_sku')
                ->get(['sc.id','sc.custom_sku','sc.old_custom_sku','scs.english_name','scs.name as color_name','sc.size','sp.spu','sp.old_spu','sc.name as scname','sc.img']);

            return $data;
        }
        if(!empty($params['spu'])){
            if (strstr($params['spu'], '，')) {
                $symbol = '，';
            } elseif (strstr($params['spu'], ',')) {
                $symbol = ',';
            } else {
                $symbol = '';
            }
            if ($symbol) {
                $cusarray = explode($symbol,$params['spu']);
                $cus_query = array();
                foreach ($cusarray as $key => $cus) {
                    $cus_query[] = $cus;
                }
                $data = $data ->where(function ($query) use($cus_query) {
                    $query->whereIn('sp.spu',$cus_query)->orwhereIn('sp.old_spu',$cus_query);
                });
                $count = $count ->where(function ($query) use($cus_query){
                    $query->whereIn('sp.spu',$cus_query)->orwhereIn('sp.old_spu',$cus_query);
                });
            } else {
                $cus = $params['spu'];
                $data = $data ->where(function ($query) use($cus) {
                    $query->where('sp.spu',$cus)->orwhere('sp.old_spu',$cus);
                });
                $count = $count ->where(function ($query) use($cus) {
                    $query->where('sp.spu',$cus)->orwhere('sp.old_spu',$cus);
                });
            }

        }

        if(!empty($params['one_cate_id'])){
            $data = $data->whereIn('sp.one_cate_id',$params['one_cate_id']);
        }

        if(!empty($params['two_cate_id'])){
            $data = $data->whereIn('sp.two_cate_id',$params['two_cate_id']);
        }

        if(!empty($params['three_cate_id'])){
            $data = $data->whereIn('sp.three_cate_id',$params['three_cate_id']);
        }

        if(!empty($params['type'])){
            $data = $data->where('sc.type',$params['type']);
        }

        if(empty($params['warehouse_id'])){
            return [
                'data' => array(),
                'totalNum' => 0,
            ];
        }


        $pagenum = isset($params['page']) ? $params['page'] : 1;
        $limit = isset($params['limit']) ? $params['limit'] : 30;

        $page  =  $pagenum- 1;
        if($page!=0){
            $page  =  $page * $limit;
        }

        // DB::connection()->enableQueryLog();

        $totalNum  = $data->groupBy('sc.custom_sku')->get()->count();
        $data = $data->offset($page)->limit($limit)
            ->orderBy('sp.spu','desc')
            ->orderby('sc.color','ASC')
            ->orderby('scsz.sort','ASC')
            ->groupBy('sc.custom_sku')
            ->get(['sc.id','sc.custom_sku','sc.old_custom_sku','sc.type','scs.english_name','scs.name as color_name','sc.size','sp.spu','sp.old_spu','sc.name as scname','sc.img','sp.one_cate_id','sp.two_cate_id','sp.three_cate_id','sc.id as custom_sku_id']);
        //    print_r(DB::getQueryLog());

        $dataJson = json_decode(json_encode($data),true);
        $custom_sku_arr = array_column($dataJson,'custom_sku_id');
        $location_data = DB::table('cloudhouse_location_num as a')
            ->leftJoin('cloudhouse_warehouse_location as b','a.location_id','=','b.id')
            ->leftJoin('platform as c','a.platform_id','=','c.Id')
            ->where('b.warehouse_id',$params['warehouse_id'])
            ->where('a.platform_id',$params['platform_id'])
            ->whereIn('a.custom_sku_id',$custom_sku_arr)
            ->select('a.custom_sku_id','b.location_name','b.location_code','a.num','b.id','a.platform_id','c.name','b.warehouse_id')
            ->get();

        $lock_data = $this->lock_inventory_d($custom_sku_arr,$params['platform_id']);
        $locationArr = array();
        $skuInstock = array();
        foreach ($location_data as $ll){

            $ll->lock_num = 0;
            if($lock_data){
                if(isset($lock_data[$ll->custom_sku_id][$ll->id][$ll->platform_id])){
                    $ll->lock_num = $lock_data[$ll->custom_sku_id][$ll->id][$ll->platform_id];
                }
            }
            if(isset($locationArr[$ll->custom_sku_id])){
                $skuInstock[$ll->custom_sku_id] += $ll->num-$ll->lock_num;
                array_push($locationArr[$ll->custom_sku_id],['location_name'=>$ll->location_name,'location_num'=>$ll->num-$ll->lock_num,
                    'location_id'=>$ll->id,'location_code'=>$ll->location_code,'platform_id'=>$ll->platform_id,'platform_name'=>$ll->name,
                    ]);
            }else{
                $skuInstock[$ll->custom_sku_id] = $ll->num-$ll->lock_num;
                $locationArr[$ll->custom_sku_id][] = ['location_name'=>$ll->location_name,'location_num'=>$ll->num-$ll->lock_num,'location_id'=>$ll->id,
                    'location_code'=>$ll->location_code,'platform_id'=>$ll->platform_id,'platform_name'=>$ll->name];
            }
            $sortArr = array_column($locationArr[$ll->custom_sku_id],'location_id');
            array_multisort($sortArr,SORT_ASC,$locationArr[$ll->custom_sku_id]);
        }


        //按照库位id升序排序
        foreach ($locationArr as $sk=>$ss){
            $sortArr = array_column($ss,'location_id');
            array_multisort($sortArr,SORT_ASC,$locationArr[$sk]);
        }


        foreach ($data as &$value) {
            $value->location_data = array();
            if(isset($locationArr[$value->custom_sku_id])){
                $value->location_data = $locationArr[$value->custom_sku_id];
            }
            if(isset($skuInstock[$value->custom_sku_id])){
                $value->intsock_num = $skuInstock[$value->custom_sku_id];
            }else{
                $value->intsock_num = 0;
            }
            $value->cate_ids = $value->one_cate_id.'-'.$value->two_cate_id.'-'.$value->three_cate_id;

            $value->small_image_url = $this->GetCustomskuImg($value->custom_sku);
            $value->product_name_cn = '';


            if($value->old_custom_sku){
                $value->client_sku = $value->old_custom_sku;
            }else{
                $value->client_sku = $value->custom_sku;
            }
            // $value->test_cus = if $value->old_custom_sku ??$value->custom_sku;

            if($value->old_spu){
                $value->spu = $value->old_spu;
            }else{
                $value->spu = $value->spu;
            }

            $value->product_name_cn = $value->scname??'';

            // $value->sellersku = $value->old_sku??$value->sku;
            // $value->shop_name = $this->GetShop($value->shop_id)['shop_name'];
        }

        return [
            'data' => $data,
            'totalNum' => $totalNum,
        ];
    }


    public function exportBarcodeCk($params){
            ////获取Excel文件数据
            $file = $params['file'];
            //spu custom_sku sku color_id size_id  platform_id one_cate three_cate shop_id name
              //获取文件后缀名
            $extension = $file->getClientOriginalExtension();
            if ($extension == 'csv') {
                $PHPExcel = new \PHPExcel_Reader_CSV();
            } elseif ($extension == 'xlsx') {
                $PHPExcel = new \PHPExcel_Reader_Excel2007();
            } else {
                $PHPExcel = new \PHPExcel_Reader_Excel5();
            }

            if (!$PHPExcel->canRead($file)) {
                return [
                    'type' => 'fail',
                    'msg' => '导入失败，Excel文件错误'
                ];
            }

            $PHPExcelLoad = $PHPExcel->load($file);
            $Sheet = $PHPExcelLoad->getSheet(0);
            /**取得一共有多少行*/
            $allRow = $Sheet->getHighestRow();

            $custom_sku_list = array();
            for ($j = 2; $j <= $allRow; $j++) {
                $custom_sku = trim($Sheet->getCellByColumnAndRow(0, $j)->getValue());
                if($custom_sku){
                    $custom_sku_list[] =  $custom_sku;
                }
            }

            return $this->getBarcodeByCk(["ex_custom_sku"=>$custom_sku_list]);
    }


    //修改条形码中文名-英文名
    public function updateBarcode($params){
        $sku = $params['sku'];

        // //中文名
        // if(isset($params['product_name_cn'])){
        //     $update['product_name_cn'] = $params['product_name_cn'];
        //     DB::table('saihe_product')->where('client_sku',$sku)->update($update);
        // }

        //英文名
        if(isset($params['title'])){
            $product =  DB::table('product_detail')->where('sellersku',$sku)->first();
            if($product){
                $updates['title'] = $params['title'];
                DB::table('product_detail')->where('sellersku',$sku)->update($updates);
            }else{
                $insert['sellersku'] = $sku;
                $insert['title'] = $params['title'];
                DB::table('product_detail')->insert($insert);
            }

        }

        return ['type'=>'success','msg'=>'修改成功'];
    }

    //仓储调拨导入
    public function BarcodeWareImport($params){
        $file = $params['file'];
        $row = ['spu','color','size','num'];
        $p['file'] = $file;
        $p['row'] = $row;
        $data = $this->CommonExcelImport($p);

        $cus_ids = [];
        $nums = [];
        foreach ($data as $v) {
            $spus = db::table('self_spu')->where('spu',$v['spu'])->orwhere('old_spu',$v['spu'])->first();
            if(!$spus){
                return['type'=>'fail','msg'=>'无此spu'];
            }
            $spu_id = $spus->id;
            $v['spu_id'] = $spu_id;

            $cus = db::table('self_custom_sku')->where('spu_id',$spu_id)->where('color',$v['color'])->where('size',$v['size'])->first();
            if(!$cus){
                return['type'=>'fail','msg'=>'无对应库存sku，请检查颜色或尺码是否填错.'.json_encode($v)];
            }

            $cus_ids[] = $cus->id;
            $nums[$cus->id] = $v['num'];
        }

        $q['cus_ids'] = $cus_ids;
        $q['warehouse_id'] = $params['warehouse_id']??1;

        $rdata = $this->getBarcodeWare($q);
        foreach ( $rdata['data'] as $v) {
            # code...
            $id = $v->id;
            $v->num = 0;
            if(isset($nums[$id])){
                $v->num = (int)$nums[$id];
            }
        }
        $rdata['type'] = 'sucess';
        return $rdata;
    }



    //仓储调拨导入-库存sku
    public function BarcodeWareCusImport($params){
        $file = $params['file'];
        $row = ['custom_sku','num'];
        $p['file'] = $file;
        $p['row'] = $row;
        $data = $this->CommonExcelImport($p);

        $cus_ids = [];
        $nums = [];
        foreach ($data as $v) {

            $cus = db::table('self_custom_sku')->where('custom_sku',$v['custom_sku'])->orwhere('old_custom_sku',$v['custom_sku'])->first();
            if(!$cus){
                return['type'=>'fail','msg'=>'无对应库存sku，请检查颜色或尺码是否填错.'.json_encode($v)];
            }

            $cus_ids[] = $cus->id;
            $nums[$cus->id] = $v['num'];
        }

        $q['cus_ids'] = $cus_ids;
        $q['warehouse_id'] = $params['warehouse_id']??1;
        $q['platform_id'] = $params['platform_id']??4;

        $rdata = $this->getBarcodeWare($q);
        foreach ( $rdata['data'] as $v) {
            # code...
            $id = $v->id;
            $v->num = 0;
            if(isset($nums[$id])){
                $v->num = (int)$nums[$id];
            }
        }
        $rdata['type'] = 'success';
        return $rdata;
    }

    //亚马逊产品数据追踪表
    public function product_trace($params){

        $page = isset($params['page']) ? $params['page'] : 1;
        $limit = isset($params['limit']) ? $params['limit'] : 20;

        //分页
        $pagenNum=$page-1;
        if ($pagenNum != 0) {
            $pagenNum = $limit * $pagenNum;
        }

        $list = DB::table('self_sku as a')
            ->leftJoin('product_detail as b','a.id','=','b.sku_id')
            ->leftJoin('self_spu as c','a.spu_id','=','c.id')
            ->where('b.now_num','>',0);



        if(isset($params['sku'])&&!empty($params['sku'])){
            if($params['select_type']=='sku'){

                $list = $list->where('a.sku','like','%'.$params['sku'].'%')->orWhere('a.old_sku','like','%'.$params['sku'].'%');

            }
            if($params['select_type']=='asin'){

                $list = $list->where('a.asin','like','%'.$params['sku'].'%');

            }
            if($params['select_type']=='fasin'){

                $list = $list->where('a.fasin','like','%'.$params['sku'].'%');

            }
            if($params['select_type']=='spu'){

                $list = $list->where('c.spu','like','%'.$params['sku'].'%')->orWhere('c.old_spu','like','%'.$params['sku'].'%');

            }
            if($params['select_type']=='fnsku'){

                $list = $list->where('b.fnsku','like','%'.$params['sku'].'%');

            }
        }
        if(isset($params['user_id'])&&!empty($params['user_id'])){
            $list = $list->where('a.user_id',$params['user_id']);
        }
        if(isset($params['shop_id'])&&!empty($params['shop_id'])){
            $list = $list->where('a.shop_id',$params['shop_id']);
        }
        if(isset($params['one_cate_id'])&&!empty($params['one_cate_id'])){
            $list = $list->whereIn('c.one_cate_id',$params['one_cate_id']);
        }
        if(isset($params['two_cate_id'])&&!empty($params['two_cate_id'])){
            $list = $list->whereIn('c.two_cate_id',$params['two_cate_id']);
        }
        if(isset($params['three_cate_id'])&&!empty($params['three_cate_id'])){
            $list = $list->whereIn('c.three_cate_id',$params['three_cate_id']);
        }
        if(isset($params['date'])&&!empty($params['date'])){
            $date = $params['date'];
        }else{
            return ['code' => 500, 'msg' => '请选择日期！'];
        }

        $totalNum = $list->count();
        DB::connection()->enableQueryLog();
        $list = $list->offset($pagenNum)
            ->limit($limit)
            ->select('a.id','a.asin','a.fasin','b.fnsku','c.spu','c.old_spu','a.sku','a.old_sku','a.user_id','a.shop_id','c.one_cate_id','c.two_cate_id','c.three_cate_id', 'a.strategy_type')
            ->get()->toArray();


        $data = $list;
        if(!empty($list)){
            $dateArr[0] = $date;
            $lastDay = strtotime('-7 days',strtotime($date));
            $dateArr[1] = date('Y-m-d',$lastDay);
            $skuArr = [];
            $fasinArr = [];
            foreach ($list as $v){
                $skuArr[] = $v->id;
                $fasinArr[] = $v->fasin;
            }
            $skuArr = array_unique($skuArr);
            $fasinArr = array_unique($fasinArr);
            $fasin = [];

            //查询fasin表
            $amazon_fasin = DB::table('amazon_fasin')->whereIn('fasin',$fasinArr)->select('type','fasin')->get()->toArray();
            foreach ($amazon_fasin as $fa){
                $fasin[$fa->fasin] = $fa->type;
            }

            //查询库龄表
            $amazon_inventory_aged = DB::table('amazon_inventory_aged')
                ->whereIn('sku_id',$skuArr)
                ->whereIn('snapshot_date',$dateArr)
                ->get()
                ->toArray();

            $thisWeek = [];
            $lastWeek = [];
            foreach ($amazon_inventory_aged as $a){
                if($a->snapshot_date==$dateArr[0]){
                    $thisWeek[$a->sku_id]['aged'] = $a;
                }
                if($a->snapshot_date==$dateArr[1]){
                    $lastWeek[$a->sku_id]['aged'] = $a;
                }
            }

            //查询领星产品表现表获取流量数据
            $lastDay2 = strtotime('-14 days',strtotime($date));
            $Lastdate = date('Y-m-d',$lastDay2);
            $lingxing_product_expression = DB::table('lingxing_product_expression')
                ->whereIn('sku_id',$skuArr)
                ->where('date','>',$Lastdate)
                ->where('date','<=',$date)
                ->select('sessions_total','date','sku_id')
                ->get()
                ->toArray();

            foreach ($lingxing_product_expression as $p){
                if($p->date>$dateArr[1]){
                    if(isset($thisWeek[$p->sku_id]['session'])){
                        $thisWeek[$p->sku_id]['session'] += $p->sessions_total;
                    }else{
                        $thisWeek[$p->sku_id]['session'] = $p->sessions_total;
                    }
                }
                if($p->date<=$dateArr[1]){
                    if(isset($lastWeek[$p->sku_id]['session'])){
                        $lastWeek[$p->sku_id]['session'] += $p->sessions_total;
                    }else{
                        $lastWeek[$p->sku_id]['session'] = $p->sessions_total;
                    }
                }
            }

//            DB::connection()->enableQueryLog();
            //获取订单数据统计销量
            $amazon_order_item = DB::table('amazon_order_item')
                ->whereIn('sku_id',$skuArr)
                ->where('amazon_time','>',$Lastdate.' 23:59:59')
                ->where('amazon_time','<=',$date.' 23:59:59')
                ->where('order_status','!=','Canceled')
                ->select('quantity_ordered','amazon_time','sku_id')
                ->get()
                ->toArray();

//            var_dump(DB::getQueryLog());

            $dataOne = $dateArr[1].' 23:59:59';
            foreach ($amazon_order_item as $r){
                if($r->amazon_time>$dataOne){
                    if(isset($thisWeek[$r->sku_id]['order'])){
                        $thisWeek[$r->sku_id]['order'] += $r->quantity_ordered;
                    }else{
                        $thisWeek[$r->sku_id]['order'] = $r->quantity_ordered;
                    }
                }
                if($r->amazon_time<=$dataOne){
                    if(isset($lastWeek[$r->sku_id]['order'])){
                        $lastWeek[$r->sku_id]['order'] += $r->quantity_ordered;
                    }else{
                        $lastWeek[$r->sku_id]['order'] = $r->quantity_ordered;
                    }
                }
            }

            //获取广告花费
            $lingxing_ad_sku_report = DB::table('lingxing_ad_sku_report')
                ->whereIn('sku_id',$skuArr)
                ->where('report_date','>',$Lastdate.' 23:59:59')
                ->where('report_date','<=',$date.' 23:59:59')
                ->select('cost','sku_id','report_date')
                ->get()
                ->toArray();

            foreach ($lingxing_ad_sku_report as $l){
                if($l->report_date>$dataOne){
                    if(isset($thisWeek[$l->sku_id]['adcost'])){
                        $thisWeek[$l->sku_id]['adcost'] += $l->cost;
                    }else{
                        $thisWeek[$l->sku_id]['adcost'] = $l->cost;
                    }
                }
                if($l->report_date<=$dataOne){
                    if(isset($lastWeek[$l->sku_id]['adcost'])){
                        $lastWeek[$l->sku_id]['adcost'] += $l->cost;
                    }else{
                        $lastWeek[$l->sku_id]['adcost'] = $l->cost;
                    }
                }
            }

            $countrysMdl = db::table('countrys')
                ->get(['Id', 'name'])
                ->keyBy('Id');

            $strategyLogMdl = db::table('sku_strategy_log')
                ->orderBy('created_at', 'DESC')
                ->get();
            $newLogList = [];
            $allLogList = [];
            foreach ($strategyLogMdl as $log) {
                $log->strategy_name = Constant::SKU_STRATEGY_TYPE[$log->strategy_type] ?? '暂无策略';
                $log->old_strategy_name = Constant::SKU_STRATEGY_TYPE[$log->old_strategy_type] ?? '暂无策略';
                $log->user_name = $this->GetUsers($log->user_id)['account'] ?? '';
                if (!isset($allLogList[$log->sku_id])){
                    $newLogList[$log->sku_id] = $log;
                }
                $allLogList[$log->sku_id][] = $log;
            }
            $skuStrategyNum = 0;
            $skuStrategyExecuteNum = 0;
            foreach ($data as $k=>$d){
                $shop = $this->GetShop($d->shop_id) ?? [];
                $countryId = $shop['country_id'] ?? 0;
                $data[$k]->country_name = $countrysMdl[$countryId]->name ?? '';
                $data[$k]->country_id = $countryId;
                $data[$k]->shop_name = $shop['shop_name'] ?? '';
                $data[$k]->user_name = $this->GetUsers($d->user_id)['account']??'';
                $data[$k]->one_cate = $this->GetCategory($d->one_cate_id)['name']??'';
                $data[$k]->two_cate = $this->GetCategory($d->two_cate_id)['name']??'';
                $data[$k]->three_cate = $this->GetCategory($d->three_cate_id)['name']??'';
                if(isset($fasin[$d->fasin])){
                    $data[$k]->fasin_type = $fasin[$d->fasin];
                }else{
                    $data[$k]->fasin_type = 0;
                }
                if(isset($thisWeek[$d->id]['aged'])){
                    $data[$k]->this_day_0_90 = $thisWeek[$d->id]['aged']->day_0_90;
                    $data[$k]->this_day_91_180 = $thisWeek[$d->id]['aged']->day_91_180;
                    $data[$k]->this_day_181_270 = $thisWeek[$d->id]['aged']->day_181_270;
                    $data[$k]->this_day_271_365 = $thisWeek[$d->id]['aged']->day_271_365;
                    $data[$k]->this_day_365 = $thisWeek[$d->id]['aged']->day_365;
                    $data[$k]->this_sale_price = $thisWeek[$d->id]['aged']->sales_price;
                    $data[$k]->this_inventory = $thisWeek[$d->id]['aged']->in_stock_num + $thisWeek[$d->id]['aged']->in_bound_num + $thisWeek[$d->id]['aged']->transfer_num;
                    $data[$k]->this_fasin = $thisWeek[$d->id]['aged']->father_asin;
                }else{
                    $data[$k]->this_day_0_90 = 0;
                    $data[$k]->this_day_91_180 = 0;
                    $data[$k]->this_day_181_270 = 0;
                    $data[$k]->this_day_271_365 = 0;
                    $data[$k]->this_day_365 = 0;
                    $data[$k]->this_sale_price = 0;
                    $data[$k]->this_inventory = 0;
                    $data[$k]->this_fasin = '';
                }
                // 2.当前产品存在高库龄产品 判断条件：存在大于180天以上的库存
                if ($data[$k]->this_day_181_270 > 0 || $data[$k]->this_day_271_365 > 0 || $data[$k]->this_day_365 > 0){
                    $data[$k]->this_is_high_storage_age = '高库龄';
                }else{
                    $data[$k]->this_is_high_storage_age = '正常';
                }

                if(isset($lastWeek[$d->id]['aged'])){
                    $data[$k]->last_day_0_90 = $lastWeek[$d->id]['aged']->day_0_90;
                    $data[$k]->last_day_91_180 = $lastWeek[$d->id]['aged']->day_91_180;
                    $data[$k]->last_day_181_270 = $lastWeek[$d->id]['aged']->day_181_270;
                    $data[$k]->last_day_271_365 = $lastWeek[$d->id]['aged']->day_271_365;
                    $data[$k]->last_day_365 = $lastWeek[$d->id]['aged']->day_365;
                    $data[$k]->last_sale_price = $lastWeek[$d->id]['aged']->sales_price;
                    $data[$k]->last_inventory = $lastWeek[$d->id]['aged']->in_stock_num + $lastWeek[$d->id]['aged']->in_bound_num + $lastWeek[$d->id]['aged']->transfer_num;
                    $data[$k]->last_fasin = $lastWeek[$d->id]['aged']->father_asin;
                }else{
                    $data[$k]->last_day_0_90 = 0;
                    $data[$k]->last_day_91_180 = 0;
                    $data[$k]->last_day_181_270 = 0;
                    $data[$k]->last_day_271_365 = 0;
                    $data[$k]->last_day_365 = 0;
                    $data[$k]->last_sale_price = 0;
                    $data[$k]->last_inventory = 0;
                    $data[$k]->last_fasin = '';
                }
                // 2023-12-18 过滤库龄数量为0的数据
                $isShow = 0;
                if (in_array('day_0_90', $params['aged_list'])){
                    if ($data[$k]->last_day_0_90 != 0 || $data[$k]->this_day_0_90 != 0){
                        $isShow = 1;
                    }
                }
                if (in_array('day_91_180', $params['aged_list'])){
                    if ($data[$k]->last_day_91_180 != 0 || $data[$k]->this_day_91_180 != 0){
                        $isShow = 1;
                    }
                }
                if (in_array('day_181_270', $params['aged_list'])){
                    if ($data[$k]->last_day_181_270 != 0 || $data[$k]->this_day_181_270 != 0){
                        $isShow = 1;
                    }
                }
                if (in_array('day_271_365', $params['aged_list'])){
                    if ($data[$k]->last_day_271_365 != 0 || $data[$k]->this_day_271_365 != 0){
                        $isShow = 1;
                    }
                }
                if (in_array('day_365', $params['aged_list'])){
                    if ($data[$k]->last_day_365 != 0 || $data[$k]->this_day_365 != 0){
                        $isShow = 1;
                    }
                }
                if ($isShow != 1){
                    unset($data[$k]);
                    $totalNum -= 1;
                    continue;
                }

                if ($data[$k]->last_day_181_270 > 0 || $data[$k]->last_day_271_365 > 0 || $data[$k]->last_day_365 > 0){
                    $data[$k]->last_is_high_storage_age = '高库龄';
                }else{
                    $data[$k]->last_is_high_storage_age = '正常';
                }
                if(isset($thisWeek[$d->id]['session'])){
                    $data[$k]->this_session = $thisWeek[$d->id]['session'];
                }else{
                    $data[$k]->this_session = 0;
                }
                if(isset($lastWeek[$d->id]['session'])){
                    $data[$k]->last_session = $lastWeek[$d->id]['session'];
                }else{
                    $data[$k]->last_session = 0;
                }


                if(isset($thisWeek[$d->id]['order'])){
                    $data[$k]->this_order = $thisWeek[$d->id]['order'];
                }else{
                    $data[$k]->this_order = 0;
                }
                if(isset($lastWeek[$d->id]['order'])){
                    $data[$k]->last_order = $lastWeek[$d->id]['order'];
                }else{
                    $data[$k]->last_order = 0;
                }

                if(isset($thisWeek[$d->id]['adcost'])){
                    $data[$k]->this_adcost = round($thisWeek[$d->id]['adcost'],2);
                }else{
                    $data[$k]->this_adcost = 0;
                }

                if(isset($lastWeek[$d->id]['adcost'])){
                    $data[$k]->last_adcost = round($lastWeek[$d->id]['adcost'],2);
                }else{
                    $data[$k]->last_adcost = 0;
                }

                // 判断当前产品库存可售天数过高 计算公式：（库存可售天数=库存除以周销量/7）判断条件：可售天数>90天
                $data[$k]->this_is_high_sales_day = '正常';
                $data[$k]->last_is_high_sales_day = '正常';
                if ($data[$k]->this_order == 0){
                    $data[$k]->this_is_high_sales_day = '过高';
                }else{
                    $dayOrder = bcdiv($data[$k]->this_order, 7, 2);
                    $day = bcdiv($data[$k]->this_inventory, $dayOrder);
                    if ($day > 90){
                        $data[$k]->this_is_high_sales_day = '过高';
                    }
                }
                if ($data[$k]->last_order == 0){
                    $data[$k]->last_is_high_sales_day = '过高';
                }else{
                    $dayOrder = bcdiv($data[$k]->last_order, 7, 2);
                    $day = bcdiv($data[$k]->last_inventory, $dayOrder);
                    if ($day > 90){
                        $data[$k]->last_is_high_sales_day = '过高';
                    }
                }

                // 策略判断
                //
                //建议策略：1.降价出售
                //        2.合并链接
                //        3.加大广告
                //        4.清仓处理
                //        5.移仓换标
                //  是否执行策略判断：
                //                 1.当本周销售价小于上周销售价即降价出售
                //                 2.当本周fasin与上周fasin不同时，即做出了合并链接的操作
                //                 3.当本周广告花费大于上周广告花费时，即做出了加大广告的策略
                //                 4.当本周库存小于上周库存时，即做出了清仓处理
                //                 5.当本周库存小于上周库存时，即做出了清仓处理

                $data[$k]->strategy_log = $allLogList[$data[$k]->id] ?? [];
                $strategyType = $newLogList[$data[$k]->id] ?? [];
                $data[$k]->strategy_name = '暂无策略';
                $data[$k]->is_strategy_execute = '未执行';
                $data[$k]->strategy_time = '';
                $data[$k]->new_strategy_name = '暂无策略';
                $data[$k]->new_strategy_time = '';
                if (!empty($strategyType)){
                    $data[$k]->new_strategy_type = $strategyType->strategy_type;
                    $data[$k]->new_strategy_name = Constant::SKU_STRATEGY_TYPE[$data[$k]->new_strategy_type] ?? '未定义的策略';
                    $data[$k]->new_strategy_time = $strategyType->created_at;
                    if (strtotime($dateArr[0]) > strtotime($strategyType->created_at)){
                        $data[$k]->strategy_type = $strategyType->strategy_type;
                        $data[$k]->strategy_name = Constant::SKU_STRATEGY_TYPE[$data[$k]->strategy_type] ?? '暂无策略';
                        $data[$k]->strategy_time = $strategyType->created_at;
                    }else{
                        foreach ($data[$k]->strategy_log as $l){
                            if (strtotime($dateArr[0]) > strtotime($l->created_at)){
                                $data[$k]->strategy_type = $l->strategy_type;
                                $data[$k]->strategy_name = Constant::SKU_STRATEGY_TYPE[$data[$k]->strategy_type] ?? '暂无策略';
                                $data[$k]->strategy_time = $l->created_at;
                                break;
                            }
                        }
                    }

                    switch ($data[$k]->strategy_type){
                        case 1:
                            if ($data[$k]->this_sale_price < $data[$k]->last_sale_price){
                                $data[$k]->is_strategy_execute = '已执行';
                            }
                            break;
                        case 2:
                            if ($data[$k]->this_fasin != $data[$k]->last_fasin){
                                $data[$k]->is_strategy_execute = '已执行';
                            }
                            break;
                        case 3:
                            if ($data[$k]->this_adcost < $data[$k]->last_adcost){
                                $data[$k]->is_strategy_execute = '已执行';
                            }
                            break;
                        case 4:
                            if ($data[$k]->this_inventory < $data[$k]->last_inventory){
                                $data[$k]->is_strategy_execute = '已执行';
                            }
                            break;
                        case 5:
                            if ($data[$k]->this_inventory < $data[$k]->last_inventory){
                                $data[$k]->is_strategy_execute = '已执行';
                            }
                            break;
                        default:
                            $data[$k]->is_strategy_execute = '未执行';
                            break;
                    }
                    if ($data[$k]->strategy_type != 0){
                        $skuStrategyNum += 1;
                    }
                    if ($data[$k]->is_strategy_execute == '已执行'){
                        $skuStrategyExecuteNum += 1;
                    }
                }
            }

            return ['code' => 200, 'msg' => '获取成功！', 'data' => ['data'=>array_values($data),'totalNum'=>$totalNum, 'sku_strategy_num' => $skuStrategyNum, 'sku_strategy_execute_num' => $skuStrategyExecuteNum]];
        }else{
            return ['code' => 200, 'msg' => '获取成功！','data' => ['data'=>array_values($data),'totalNum'=>0]];
        }
    }

    public function saveSkuStrategy($params)
    {
        // try {
            if (!isset($params['sku_ids']) || !is_array($params['sku_ids'])){
                throw new \Exception('未给定SKU标识或给定的格式不正确！');
            }
            if (!isset($params['strategy_type']) || !isset(Constant::SKU_STRATEGY_TYPE[$params['strategy_type']])){
                throw new \Exception('未给定建议策略或给定的建议策略非预设值！');
            }
            if (!isset($params['user_id']) || empty($params['user_id'])){
                if (isset($params['token']) && !empty($params['token'])){
                    $users = db::table('users')->where('token',$params['token'])->first();
                    if($users){
                        $params['user_id'] = $users->Id;
                    }else{
                        throw new \Exception('未给定创建人标识！');
                    }
                }else{
                    throw new \Exception('未给定创建人标识！');
                }
            }

            $max_id = 10000;
            $strategy_task_id = db::table('sku_strategy_log')->max('task_id');
            if($strategy_task_id>$max_id){
                $max_id = $strategy_task_id;
            }
            $s_task_id = $max_id+1;
            $skuMdl = db::table('self_sku')
                ->get(['id', 'sku','asin','strategy_type','fasin'])
                ->keyBy('id');
            db::beginTransaction();
            $asins = [];
            $desc = $params['desc'];
            foreach ($params['sku_ids'] as $sk){
                $id = $sk;
        
                if (!isset($skuMdl[$id])){
                    throw new \Exception('sku标识：'.$id.'不存在!');
                }
                $sku = $skuMdl[$id];
                $asins[] = $sku->asin;
                $fasin = $sku->fasin;
                $update = db::table('self_sku')
                    ->where('id', $id)
                    ->update([
                        'strategy_type' => $params['strategy_type']
                    ]);
                if (!empty($update)){
                    $insert = db::table('sku_strategy_log')
                        ->insertGetId([
                            'sku_id' => $sku->id,
                            'sku' => $sku->sku,
                            'asin'=>$sku->asin,
                            'fasin'=>$sku->fasin,
                            'desc'=>$desc,
                            'old_strategy_type' => $sku->strategy_type,
                            'strategy_type' => $params['strategy_type'],
                            'user_id' => $params['user_id'],
                            'created_at' => date('Y-m-d H:i:s', microtime(true)),
                            'task_id'=>$s_task_id
                        ]);
                    if (empty($insert)){
                        throw new \Exception('sku标识：'.$id.'，sku：'.$sku->sku.'策略变更日志保存失败!');
                    }
                }
            }
            $amzon_fasin = db::table('amazon_fasin')->where('fasin',$fasin)->first();
            if(!$amzon_fasin){
                db::rollback();
                return ['code' => 500, 'msg' => '无fasin'];
            }
            db::commit();



            db::table('sku_strategy_log')->where('task_id',$s_task_id)->update(['fasin_user_id'=>$amzon_fasin->user_id]);
            $request_link = '/product_trace_detail?id='.$s_task_id;
            $request_id =  $s_task_id;


            $addtask['task_t_id'] = 22459;
            $addtask['user_fz'] = $amzon_fasin->user_id;
            $addtask['user_cj'] = $params['user_id'];
            $addtask['request_id'] = $request_id ;
            $addtask['link'] = $request_link;
            $strategy_name = Constant::SKU_STRATEGY_TYPE[$params['strategy_type']];
            $addtask['task_desc'] = $fasin.'链接下的'.implode(',',$asins).'产品请执行'.$strategy_name.'策略'.'('.$desc.')';
            $i_tasks_id = $this->create_task($addtask);
            if($i_tasks_id==0){
                return ['code' => 500, 'msg' => '生成任务失败'];
                // db::rollback();
            }

        // }catch (\Exception $e){
        //     db::rollback();
        //     return ['code' => 500, 'msg' => '保存失败！原因：'.$e->getMessage().'；位置：'.$e->getFile().'；'.$e->getLine()];
        // }

            $rt_data_one['name'] ='';
            $task = db::table('tasks')->where('id', $i_tasks_id)->first();
            if($task){
                $rt_data_one['name'] = $task->name;
                $rt_data_one['desc'] = $task->desc;
                $rt_data_one['task_id'] = $i_tasks_id;
            }

            $rt_data[] = $rt_data_one;


        return ['code' => 200, 'msg' => '保存成功！','data' => $rt_data];
    }


    public function strategy_task($params){
        $taks_id = $params['id'];
        $list = db::table('sku_strategy_log')->where('task_id',$taks_id)->get();
        foreach ($list as $v) {
            # code...
            $v->user = $this->GetUsers($v->user_id)['account']??'';
        }
        return['code'=>200,'data'=>$list,'msg'=>'获取成功'];
    }



    public function create_task($param){

        //1.同安仓 2.泉州仓 3.云仓
       // var_dump($param);
       // return 1;
       $task = new \App\Libs\wrapper\Task();

       $taskinfo =  $task->task_info(['task_id'=>$param['task_t_id']]);


       $res = $taskinfo['basic'];

       
       $res['node'] = $taskinfo['node'];


       // unset($taskinfo['usef_fz']);
       // var_dump($taskinfo);
       $res['user_fz'] =  $param['user_fz'];

       $res['user_cj'] =  $param['user_cj'];

       
       //节点1创货件负责人
       $res['node'][0]['node_task_data'][0]['account'] = $this->GetUsers($res['user_fz'])['account'];
       $res['node'][0]['node_task_data'][0]['phone'] = $this->GetUsers($res['user_fz'])['phone'];
       $res['node'][0]['node_task_data'][0]['user_id'] = $res['user_fz'];
       $res['node'][0]['node_task_data'][0]['task'] = $param['task_desc'];
       



       $res['Id'] = $param['task_t_id'];

       // var_dump($param['request_id']);
       $res['name'] = $taskinfo['basic']['name'].$param['request_id'].'-'.rand(1,100);
       // unset($taskinfo['basic']['name']);
       // var_dump( $res['name']);

       $ext['request_link'] =$param['link'];
       $ext['request_id']=(string)$param['request_id'];

       $res['ext'] = $ext;

       unset($res['examine']);
       // foreach ($taskinfo['basic']['examine'] as $v) {
       //     # code...
       //     $res['examine'][] = $v['user_id'];

       // }
    //    $res['examine'] = $param['user_sh'];

       // unset($taskinfo['basic']['examine']);
      
       // $res['user_fz'] = db::table('users')->where('account',$taskinfo['account_fz'])->first()->Id;

       // unset($taskinfo['account_fz']);
       $res['state'] = 1;
       $res['is_intervention'] = 2;


       // var_dump($res);
   
       $id = $task->task_add($res);

       // var_dump($id);

       if($id['type']=='success'){
           return $id['data'];
       }else{
           return 0;
       }
       // $res[]

       // state: 1
       // is_intervention: 2

   }



   public function getStrategyByUser($params){

        $start_time = $params['start_time']??date('Y-m-d',time());
        $end_time = $params['end_time']??date('Y-m-d',time());
        $start_time = $start_time.' 00:00:00';
        $end_time = $end_time.' 23:59:59';

        $pagenum = isset($params['page']) ? $params['page'] : 1;
        $limit = isset($params['limit']) ? $params['limit'] : 30;

        $page  =  $pagenum- 1;
        if($page!=0){
            $page  =  $page * $limit;
        }



        $list = db::table('sku_strategy_log')->whereBetween('created_at', [$start_time, $end_time])->groupby('fasin')->select(db::raw('group_concat(task_id) as task_ids'),'fasin_user_id','fasin');
    
        if(isset($params['user_id'])){
            $list = $list->where('fasin_user_id',$params['user_id']);
        }
        $list = $list->offset($page)->limit($limit)->get();

        $rdata =[];
        $i = 0;
        foreach ($list as $v) {
            # code...
            $rdata[$v->fasin_user_id]['user_id'] = $v->fasin_user_id;
            $rdata[$v->fasin_user_id]['user'] = $this->GetUsers($v->fasin_user_id)['account']; 
            $task_id= max(explode(',',$v->task_ids));
            $tasks = db::table('sku_strategy_log')->where('task_id',$task_id)->first();
            $detail['task_id'] = $task_id;
            $detail['fasin'] = $tasks->fasin;
            $detail['strategy_type'] = $tasks->strategy_type;
            $detail['strategy_name'] = Constant::SKU_STRATEGY_TYPE[$tasks->strategy_type] ?? '暂无策略';
            $detail['created_at'] = $tasks->created_at;
            $detail['end_time'] = $tasks->end_time;
            $detail['status'] = $tasks->status;
            $rdata[$v->fasin_user_id]['detail'][] = $detail;
            $i++;

        }

        $rdata = array_values($rdata);


        foreach ($rdata as &$rv) {
            $num = 0;
            $no_num = 0;
            $num_arr = [];
            $no_num_arr = [];
            foreach ($rv['detail'] as $dv) {
                # code...
                if($dv['status']==0){
                    $no_num_arr[] = $dv;
                    $no_num++;
                }else{
                    $num_arr[] =  $dv;
                    $num++;
                }
            }

            $rv['num'] = $num;
            $rv['no_num'] = $no_num;
            $rv['no_num_arr'] = $no_num_arr;
            $rv['num_arr'] = $num_arr;
            # code...
        }
        $data['total_num'] = count($rdata);
        $data['data'] =  array_slice($rdata,$page,$limit);

     

        return ['type'=>'success','data'=>$data];
   
   }



}
